package com.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.dao.impl.TransDao;
import com.service.TransService;
import com.util.ExcleCellStyle;

public class TransServiceImpl implements TransService {
	TransDao dao = new TransDao();

	public void downloadExcel(String owner,String type, String url, String username, String password,
			String fileName, String saveUrl) {
		System.out.println("******************* downloadExcel start ****************************");
		OutputStream os = null;
		try {
			os = new FileOutputStream(new File(saveUrl));
			Workbook wb = new HSSFWorkbook();
			List<Map<String, String>> divList = dao.getMenuData(owner,type, url, username, password);
			createMenuSheet(wb, divList);
			createDataSheet(wb, divList,owner,type, url,  username,  password);
			wb.write(os);
			os.flush();
		} catch (Exception e) {
			System.out.println(e);
			e.printStackTrace();
		} finally {
			System.out.println("******************* downloadExcel end ****************************");
			try {
				if (os != null) {
					os.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void createDataSheet(Workbook wb, List<Map<String, String>> divList,String owner,String type, String url, String username, String password) {
		System.out.println("******************* createDataSheet start ****************************");
		CellStyle titleStyle = ExcleCellStyle.cellStyle(wb, "title");
		CellStyle cellStyle = ExcleCellStyle.cellStyle(wb, null);
		Sheet sheet = wb.createSheet("表结构说明");
		sheet.setDefaultColumnWidth(20);
		CellRangeAddress region = null;
		try {
			int count =0;
			for (int i = 0; i < divList.size(); i++) {
				
				Row row = sheet.createRow(count);
				row.setHeightInPoints(20);
				Cell cell = row.createCell(0);
				cell.setCellValue("表名");
				cell.setCellStyle(titleStyle);
				region = new CellRangeAddress(count,count, 1, 2);
				sheet.addMergedRegion(region);
				cell = row.createCell(1);
				cell.setCellValue(divList.get(i).get("TABLE_NAME"));
				cell.setCellStyle(cellStyle);
				cell = row.createCell(3);
				cell.setCellValue("注释");
				cell.setCellStyle(titleStyle);
				region = new CellRangeAddress(count,count, 4, 5);
				sheet.addMergedRegion(region);
				cell = row.createCell(4);
				cell.setCellValue(divList.get(i).get("COMMENTS"));
				cell.setCellStyle(cellStyle);
				cell = row.createCell(6);
				cell.setCellValue("表类型<表、视图、等>");
				cell.setCellStyle(titleStyle);
				region = new CellRangeAddress(count,count, 7, 8);
				sheet.addMergedRegion(region);
				cell = row.createCell(7);
				cell.setCellValue(divList.get(i).get("TABLE_TYPE"));
				cell.setCellStyle(cellStyle);
				count++;
				row = sheet.createRow(count);
				row.setHeightInPoints(20);
				cell = row.createCell(0);
				cell.setCellValue("列ID");
				cell.setCellStyle(titleStyle);
				cell = row.createCell(1);
				cell.setCellValue("字段名");
				cell.setCellStyle(titleStyle);
				cell = row.createCell(2);
				cell.setCellValue("注释");
				cell.setCellStyle(titleStyle);
				cell = row.createCell(3);
				cell.setCellValue("字段类型");
				cell.setCellStyle(titleStyle);
				cell = row.createCell(4);
				cell.setCellValue("数据长度");
				cell.setCellStyle(titleStyle);
				cell = row.createCell(5);
				cell.setCellValue("小数点位数");
				cell.setCellStyle(titleStyle);
				cell = row.createCell(6);
				cell.setCellValue("是否允许为空");
				cell.setCellStyle(titleStyle);
				cell = row.createCell(7);
				cell.setCellValue("默认值");
				cell.setCellStyle(titleStyle);
				cell = row.createCell(8);
				cell.setCellValue("主键");
				cell.setCellStyle(titleStyle);
				count++;
				// 写入数据
				List<Map<String, String>> list = dao.getTableDate(owner,type, url, username, password,divList.get(i).get("TABLE_NAME"));
				for (int j=0; j < list.size(); j++) {
						row = sheet.createRow(count);
						row.setHeightInPoints(20);
						cell = row.createCell(0);
						cell.setCellValue(list.get(j).get("COLUMN_ID"));
						cell.setCellStyle(cellStyle);
						cell = row.createCell(1);
						cell.setCellValue(list.get(j).get("COLUMN_NAME"));
						cell.setCellStyle(cellStyle);
						cell = row.createCell(2);
						cell.setCellValue(list.get(j).get("COMMENTS"));
						cell.setCellStyle(cellStyle);
						cell = row.createCell(3);
						cell.setCellValue(list.get(j).get("DATA_TYPE"));
						cell.setCellStyle(cellStyle);
						cell = row.createCell(4);
						cell.setCellValue(list.get(j).get("DATA_LENGTH"));
						cell.setCellStyle(cellStyle);
						cell = row.createCell(5);
						cell.setCellValue(list.get(j).get("DATA_SCALE"));
						cell.setCellStyle(cellStyle);
						cell = row.createCell(6);
						cell.setCellValue(list.get(j).get("NULLABLE"));
						cell.setCellStyle(cellStyle);
						cell = row.createCell(7);
						cell.setCellValue(list.get(j).get("DATA_DEFAULT"));
						cell.setCellStyle(cellStyle);
						cell = row.createCell(8);
						cell.setCellValue(list.get(j).get("PrimaryKey"));
						cell.setCellStyle(cellStyle);
						count++;
				}
				row = sheet.createRow(count);
				row.setHeightInPoints(40);
				count++;
			}
		} catch (NumberFormatException e) {
			System.out.println(e);
			e.printStackTrace();
		} finally {
			System.out.println("******************* createDataSheet end ****************************");
		}
	}

	public void createMenuSheet(Workbook wb, List<Map<String, String>> divList) {
		System.out.println("******************* createMenuSheet start ****************************");
		CellStyle titleStyle = ExcleCellStyle.cellStyle(wb, "title");
		CellStyle cellStyle = ExcleCellStyle.cellStyle(wb, null);
		Sheet sheet = wb.createSheet("目录");

		// 目录
		CellRangeAddress region = new CellRangeAddress(0,0, 0, 3);
		sheet.addMergedRegion(region);
		Row row = sheet.createRow(0);
		row.setHeightInPoints(20);
		Cell cell = row.createCell(0);
		cell.setCellValue("目录");
		cell.setCellStyle(titleStyle);
		row = sheet.createRow(1);
		cell = row.createCell(0);
		cell.setCellValue("序号");
		cell.setCellStyle(titleStyle);
		sheet.setColumnWidth(1, 50 * 256);
		cell = row.createCell(1);
		cell.setCellValue("表名");
		cell.setCellStyle(titleStyle);
		sheet.setColumnWidth(2, 50 * 256);
		cell = row.createCell(2);
		cell.setCellValue("注释");
		cell.setCellStyle(titleStyle);
		cell = row.createCell(3);
		cell.setCellValue("表类型<表、视图、等>");
		cell.setCellStyle(titleStyle);
		try {
			for (int i = 0; i < divList.size(); i++) {
				row = sheet.createRow(i + 1);
				row.setHeightInPoints(20);
				cell = row.createCell(0);
				cell.setCellValue(i+1);
				cell.setCellStyle(cellStyle);
				cell = row.createCell(1);
				cell.setCellValue(divList.get(i).get("TABLE_NAME"));
				cell.setCellStyle(cellStyle);
				cell = row.createCell(2);
				cell.setCellValue(divList.get(i).get("COMMENTS"));
				cell.setCellStyle(cellStyle);
				cell = row.createCell(3);
				cell.setCellValue(divList.get(i).get("TABLE_TYPE"));
				cell.setCellStyle(cellStyle);
			}
		} catch (FormulaParseException e) {
			e.printStackTrace();
		} finally {
			System.out.println("******************* createMenuSheet end ****************************");
		}
	}
}
